-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,oracle语法的函数，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop function if exists func_add_sql;
drop function if exists func_date;
drop function if exists func_time;
drop function if exists func_timestamptz;
drop function if exists func_run;
drop function if exists func_add_sql;
drop function if exists func_date1;
drop function if exists func_date2;
drop function if exists func_date3;
drop function if exists func_date4;
drop function if exists func_date5;
drop function if exists func_date6;
drop function if exists func_date7;
drop function if exists func_date8;
drop function if exists func_date9;
drop function if exists func_date10;
drop function if exists func_date11;
drop function if exists func_date12;
drop function if exists func_date13;
drop function if exists func_date14;
drop function if exists func_date15;
create extension gms_output;
--case1
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;


set behavior_compat_options='skip_insert_gs_source,select_into_return_null,proc_outparam_override';
create or replace function func_add_sql(a1 integer, a2 integer) RETURN integer result_cache IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: excute function...';
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid WHERE p.proname = 'func_add_sql' order by p.proname;
select func_add_sql(12,10);
--case2 bug1052549
DECLARE
x INTEGER;
BEGIN
raise notice 'NOTICE: excute function...';
gms_output.put_line('Execution returns here (2).');
END;
/
DECLARE
x INTEGER;
FUNCTION f (n INTEGER)
RETURN INTEGER
IS
BEGIN
raise notice 'NOTICE: excute function...';
RETURN (n*n);
END;
BEGIN
raise notice 'NOTICE: excute function...';
gms_output.put_line (
'f returns ' || f(2) || '. Execution returns here (1).'
);
x := f(2);
gms_output.put_line('Execution returns here (2).');
END;
/
--new add
drop table if exists all_support_type;
create table all_support_type(
id int,
col_int int,
col_date date,
col_time time,
col_timestamptz timestamptz
);
insert into all_support_type values( 5, 5,' 2011-01-01 00:00:00 ',' 10:12:13 ',' 2015-01-01 00:00:00');
insert into all_support_type values(20,20,' 2011-01-01 00:00:00 ',' 10:12:13 ',' 2015-01-01 00:00:00');
insert into all_support_type values(15,15,' 2011-01-01 00:00:00 ',' 10:12:13 ',' 2015-01-01 00:00:00');
insert into all_support_type values(10,10,' 2011-01-01 00:00:00 ',' 10:12:13 ',' 2015-01-01 00:00:00');
insert into all_support_type values(11,11,' 2011-01-02 00:00:00 ',' 10:12:14 ',' 2015-01-02 00:00:00');
insert into all_support_type values( 6, 6,' 2011-01-02 00:00:00 ',' 10:12:14 ',' 2015-01-02 00:00:00');
insert into all_support_type values( 1, 1,' 2011-01-02 00:00:00 ',' 10:12:14 ',' 2015-01-02 00:00:00');
insert into all_support_type values(16,16,' 2011-01-02 00:00:00 ',' 10:12:14 ',' 2015-01-02 00:00:00');

select count(*) from all_support_type;
select * from all_support_type;

--create function
--date
drop function if exists func_date(date);
create or replace function func_date(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+12;
end;
$$ language plpgsql stable result_cache;
/

select func_date('2020-1-1'::date) from all_support_type;
select func_date(col_date) from all_support_type;

--time
drop function if exists func_time(time);
create or replace function func_time(p time) returns time as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '10:12:13' then
gms_output.put_line('param:'||p);
end if;
return p::time+interval '3 hours';
end;
$$ language plpgsql immutable result_cache;
/

select func_time('10:12:14'::time) from all_support_type;
select func_time(col_time) from all_support_type;

--timestamptz
drop function if exists func_timestamptz(timestamptz);
create or replace function func_timestamptz(p timestamptz) returns timestamptz as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::timestamptz+12;
end;
$$ language plpgsql stable result_cache;
/

select func_timestamptz('2020-1-1'::timestamptz) from all_support_type;
select func_timestamptz(col_timestamptz) from all_support_type;

--自治事务中调用函数
drop function if exists func_run(date);
create or replace function func_run(d1 date) RETURN date result_cache IS
v_cnt integer;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
return func_timestamptz(d1);
commit;
end;
/

--select func_run('2020-1-1'::date) from all_support_type;
--select func_run(col_date) from all_support_type;


--自治事务
drop function if exists func_add_sql(integer,integer);
create or replace function func_add_sql(a1 integer, a2 integer) RETURN integer result_cache IS
v_cnt integer;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
raise notice 'NOTICE: excute function  ...';
v_cnt:=a1+a2;
RETURN v_cnt;
commit;
end;
/

--select func_add_sql(12,10);
--select func_add_sql(id,col_int) from all_support_type;

--函数个数限制
drop function if exists func_date1(date);
create or replace function func_date1(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+1;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date2(date);
create or replace function func_date2(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+2;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date3(date);
create or replace function func_date3(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+3;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date4(date);
create or replace function func_date4(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+4;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date5(date);
create or replace function func_date5(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+5;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date6(date);
create or replace function func_date6(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+6;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date7(date);
create or replace function func_date7(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+7;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date8(date);
create or replace function func_date8(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+8;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date9(date);
create or replace function func_date9(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+9;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date10(date);
create or replace function func_date10(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+10;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date11(date);
create or replace function func_date11(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+11;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date12(date);
create or replace function func_date12(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+12;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date13(date);
create or replace function func_date13(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+13;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date14(date);
create or replace function func_date14(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+14;
end;
$$ language plpgsql immutable result_cache;
/

drop function if exists func_date15(date);
create or replace function func_date15(p date) returns date as $$
begin
raise notice 'NOTICE: excute function  ...';
if p = '2011-1-2' then
gms_output.put_line('param:'||p);
end if;
return p::date+15;
end;
$$ language plpgsql immutable result_cache;
/

select func_date1(col_date),func_date2(col_date),func_date3(col_date),func_date4(col_date),func_date5(col_date),func_date6(col_date),func_date7(col_date),func_date8(col_date),func_date9(col_date),func_date10(col_date),func_date11(col_date),func_date12(col_date),func_date13(col_date) from all_support_type;

select func_date1(col_date),func_date2(col_date),func_date3(col_date),func_date4(col_date),func_date5(col_date),func_date6(col_date),func_date7(col_date),func_date8(col_date),func_date9(col_date),func_date10(col_date),func_date11(col_date),func_date12(col_date),func_date13(col_date),func_date14(col_date),func_date15(col_date) from all_support_type;

select func_date1(col_date),func_date2(col_date),func_date3(col_date),func_date4(col_date),func_date5(col_date),func_date6(col_date),func_date7(col_date),func_date8(col_date),func_date9(col_date),func_date10(col_date),func_date11(col_date),func_date12(col_date),func_date13(col_date),func_date14(col_date),func_date15(col_date) from all_support_type;

select func_date1('2023-1-1'),func_date2('2023-1-1'),func_date3('2023-1-1'),func_date4('2023-1-1'),func_date5('2023-1-1'),func_date6('2023-1-1'),func_date7('2023-1-1'),func_date8('2023-1-1'),func_date9('2023-1-1'),func_date10('2023-1-1'),func_date11('2023-1-1'),func_date12('2023-1-1'),func_date13('2023-1-1') from all_support_type;

select func_date1('2023-1-1'),func_date2('2023-1-1'),func_date3('2023-1-1'),func_date4('2023-1-1'),func_date5('2023-1-1'),func_date6('2023-1-1'),func_date7('2023-1-1'),func_date8('2023-1-1'),func_date9('2023-1-1'),func_date10('2023-1-1'),func_date11('2023-1-1'),func_date12('2023-1-1'),func_date13('2023-1-1'),func_date14('2023-1-1') from all_support_type;

select func_date1('2023-1-1'),func_date2('2023-1-1'),func_date3('2023-1-1'),func_date4('2023-1-1'),func_date5('2023-1-1'),func_date6('2023-1-1'),func_date7('2023-1-1'),func_date8('2023-1-1'),func_date9('2023-1-1'),func_date10('2023-1-1'),func_date11('2023-1-1'),func_date12('2023-1-1'),func_date13('2023-1-1'),func_date14('2023-1-1'),func_date15('2023-1-1') from all_support_type;
--teardown
drop function if exists func_add_sql;
drop function if exists func_date;
drop function if exists func_time;
drop function if exists func_timestamptz;
drop function if exists func_run;
drop function if exists func_add_sql;
drop function if exists func_date1;
drop function if exists func_date2;
drop function if exists func_date3;
drop function if exists func_date4;
drop function if exists func_date5;
drop function if exists func_date6;
drop function if exists func_date7;
drop function if exists func_date8;
drop function if exists func_date9;
drop function if exists func_date10;
drop function if exists func_date11;
drop function if exists func_date12;
drop function if exists func_date13;
drop function if exists func_date14;
drop function if exists func_date15;
